<?php
/**
 * @desc access for MySQL Database
 * @author HoangNV2
 */
class SysMySql {
	public $config = array();
	public $conn = NULL;
	public $sql = '';
	public $recordset;
	public $result;
	public $oid;	// last insert id
	public $connection = NULL;

	public function __construct() {

	}
	/**
	 *using to connect to database
	 *@param $config
	 */
	function connect () {
		$host = $this->config['host'];
		$user = $this->config['username'];
		$pass = $this->config['password'];
		$port = $this->config['port'];
		$dbname = $this->config['dbname'];
		
		if (!function_exists('mysql_connect')) $this->exceptionError('Do not support database mysql');
		
		$this->connection = @mysql_connect($host.':'.$port,$user,$pass) OR $this->exceptionError(mysql_error());
		$this->conn = @mysql_select_db($dbname,$this->connection) OR $this->exceptionError(mysql_error());
	}

	function getConn () {
		if ($this->conn == NULL) $this->connect();
		return $this->conn;
	}
	/**
	 * using to query a sql statement
	 */
	function query($sql=''){
		$this->sql = $sql;
		if ($this->conn == NULL) $this->connect();
		$this->recordset = @mysql_query($sql) OR $this->exceptionError(mysql_error());
		return $this->recordset;
	}

	/**
	 * @desc use INSERT,DELETE,UPDATE
	 * @return:
	 * 		- ROLLBACK if false
	 * 		- COMMIT if true
	 */
	public function queryRollback ($sql='') {
		$this->sql = $sql;
		if ($this->conn == NULL) $this->connect();

		@mysql_query("START TRANSACTION");
		$res = @mysql_query($sql) OR $this->exceptionError(mysql_error(), 'ROLLBACK');
		@mysql_query("COMMIT");
		$this->recordset = $res;
		
		return $this->recordset;
	}

	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function fetch ($sql) {
		$this->query($sql);
		$this->result = array();
		while ($row = mysql_fetch_row($this->recordset)) {
			$this->result[] = $row;
		}
		return $this->result;
	}

	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function fetchAll ($sql) {
		$this->query($sql);
		while ($row = mysql_fetch_assoc($this->recordset)){
			$this->result[] = $row;
		}
		return $this->result;
	}

	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function selectOne ($sql) {
		$sql.= "\n LIMIT 1";
		$this->query($sql);
		$this->result = mysql_fetch_assoc($this->recordset);
		return $this->result;
	}

	/**
	 * @param string $sql SQL QUERY
	 * @param string $key key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function select ($sql='',$key='device_name',$page_record=NULL,$page_no=NULL) {
		$this->query($sql);
		if ($key == '') {
			while ($row = mysql_fetch_assoc($this->recordset)) {
				$this->result[] = $row;
			}
		} else {
			$this->result = array();
			while ($row = mysql_fetch_assoc($this->recordset)) {
				if (!isset($row[$key])) $this->exceptionError('Do not exist key ['.$key.']');

				$this->result[$row[$key]][] = $row;
			}
		}
		return $this->result;
	}


	/**
	 * @return nums row
	 */
	public function getNumRows ($res=NULL) {
		if (is_null($res)) $res = $this->recordset;
		if ($res == '') return 0;
		return mysql_num_rows($res);
	}

	/**
	 * @desc insert one record
	 * @return array new record id OR false
	 */
	public function insert ($sql='', $key_name='') {
		$res = $this->queryRollback($sql);
		if ($this->recordset) return true;
		return false;
	}

	/**
	 * @return last insert id
	 */
	public function getLastId () {
		return $this->oid;
	}

	/**
	 * @return true|false
	 */
	public function update ($sql,$limit='') {
		/*if ($limit != '') {
			$sql.= "\n LIMIT {$limit}";
			}*/
		$this->queryRollback($sql);
		if ($this->recordset) return true;

		return false;
	}

	public function delete ($sql, $limit='') {
		/*if ($limit != '') {
			$sql.= "\n LIMIT {$limit}";
			}*/
		$this->queryRollback($sql);
		if ($this->recordset) return true;

		return false;
	}

	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function getFetch ($res=NULL) {
		if (!$res) return ;
		$this->result = array();
		while ($row = mysql_fetch_row($res)) {
			$this->result[] = $row;
		}
		return $this->result;
	}

	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function getFetchAll ($res=NULL) {
		if (!$res) return ;

		while ($row = mysql_fetch_assoc($res)){
			$this->result[] = $row;
		}
		return $this->result;
	}

	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function getSelectOne ($res=NULL) {
		if (!$res) return ;
		$this->result = mysql_fetch_assoc($res);
		return $this->result;
	}

	/**
	 * @param string $sql SQL QUERY
	 * @param string $key key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function getSelect ($res=NULL,$key='') {
		if (!$res) return ;

		if ($key == '') {
			while ($row = mysql_fetch_assoc($res)) {
				$this->result[] = $row;
			}
		} else {
			$this->result = array();
			while ($row = mysql_fetch_assoc($res)) {
				if (!isset($row[$key])) $this->exceptionError('Do not exist key ['.$key.']');
				$this->result[$row[$key]][] = $row;
			}
		}
		return $this->result;
	}


	/**
	 * @return print error
	 */
	public function exceptionError ($msg, $act='') {
		if ($act == 'ROLLBACK') {
			@mysql_query("ROLLBACK");
		}
		throw new SysExceptionModel($msg);
	}

	/**
	 * @desc close connection
	 */
	public function __destruct () {
		// Close this connection
		@mysql_close($this->connection);
	}
}